"""
视频仓库

提供视频相关数据的专门访问方法。
"""

from typing import List, Optional, Dict, Any
from datetime import datetime, timedelta

from sqlalchemy.orm import joinedload
from sqlalchemy import and_, or_, desc, asc, func

from src.core.di import Injectable, Inject
from src.database.repository import BaseRepository
from src.models import Video, VideoMetadata, VideoClassification
from src.models.task import TaskType


@Injectable(scope="singleton")
class VideoRepository(BaseRepository[Video]):
    """
    视频仓库
    
    提供视频数据的专门访问方法和业务查询。
    """
    
    def __init__(self):
        super().__init__(Video)
    
    def get_by_path(self, file_path: str) -> Optional[Video]:
        """根据文件路径获取视频"""
        return self.get_one_by_field("file_path", file_path)
    
    def get_by_hash(self, file_hash: str) -> Optional[Video]:
        """根据文件哈希获取视频"""
        return self.get_one_by_field("file_hash", file_hash)
    
    def get_with_metadata(self, video_id: int) -> Optional[Video]:
        """获取包含元数据的视频"""
        session = self.get_session()
        try:
            return session.query(Video).options(
                joinedload(Video.video_metadata)
            ).filter(Video.id == video_id).first()
        finally:
            session.close()
    
    def get_with_classifications(self, video_id: int) -> Optional[Video]:
        """获取包含分类信息的视频"""
        session = self.get_session()
        try:
            return session.query(Video).options(
                joinedload(Video.classifications)
            ).filter(Video.id == video_id).first()
        finally:
            session.close()
    
    def get_with_all_relations(self, video_id: int) -> Optional[Video]:
        """获取包含所有关联信息的视频"""
        session = self.get_session()
        try:
            return session.query(Video).options(
                joinedload(Video.video_metadata),
                joinedload(Video.classifications),
                joinedload(Video.processing_tasks)
            ).filter(Video.id == video_id).first()
        finally:
            session.close()
    
    def find_by_size_range(self, min_size: int = None, max_size: int = None) -> List[Video]:
        """根据文件大小范围查找视频"""
        filters = {}
        if min_size is not None and max_size is not None:
            filters["file_size"] = {"gte": min_size, "lte": max_size}
        elif min_size is not None:
            filters["file_size"] = {"gte": min_size}
        elif max_size is not None:
            filters["file_size"] = {"lte": max_size}
        
        return self.find(filters=filters, order_by="file_size")
    
    def find_by_duration_range(self, min_duration: float = None, max_duration: float = None) -> List[Video]:
        """根据时长范围查找视频"""
        filters = {}
        if min_duration is not None and max_duration is not None:
            filters["duration"] = {"gte": min_duration, "lte": max_duration}
        elif min_duration is not None:
            filters["duration"] = {"gte": min_duration}
        elif max_duration is not None:
            filters["duration"] = {"lte": max_duration}
        
        return self.find(filters=filters, order_by="duration")
    
    def find_by_resolution(self, width: int = None, height: int = None) -> List[Video]:
        """根据分辨率查找视频"""
        filters = {}
        if width:
            filters["width"] = width
        if height:
            filters["height"] = height
        
        return self.find(filters=filters)
    
    def find_by_format(self, format_name: str) -> List[Video]:
        """根据格式查找视频"""
        return self.get_by_field("format", format_name)
    
    def find_unprocessed(self) -> List[Video]:
        """查找未处理的视频"""
        return self.get_by_field("is_processed", False)
    
    def find_processed(self) -> List[Video]:
        """查找已处理的视频"""
        return self.get_by_field("is_processed", True)
    
    def find_unavailable(self) -> List[Video]:
        """查找不可用的视频"""
        return self.get_by_field("is_available", False)
    
    def find_recent(self, days: int = 7) -> List[Video]:
        """查找最近添加的视频"""
        cutoff_date = datetime.utcnow() - timedelta(days=days)
        session = self.get_session()
        try:
            return session.query(Video).filter(
                Video.created_at >= cutoff_date
            ).order_by(desc(Video.created_at)).all()
        finally:
            session.close()
    
    def find_large_files(self, size_mb: float = 100) -> List[Video]:
        """查找大文件"""
        size_bytes = int(size_mb * 1024 * 1024)
        return self.find_by_size_range(min_size=size_bytes)
    
    def find_by_category(self, category: str) -> List[Video]:
        """根据分类查找视频"""
        session = self.get_session()
        try:
            return session.query(Video).join(VideoClassification).filter(
                VideoClassification.category == category
            ).all()
        finally:
            session.close()
    
    def find_by_classifier(self, classifier_type: str) -> List[Video]:
        """根据分类器类型查找视频"""
        session = self.get_session()
        try:
            return session.query(Video).join(VideoClassification).filter(
                VideoClassification.classifier_type == classifier_type
            ).all()
        finally:
            session.close()
    
    def find_high_confidence(self, min_confidence: float = 0.8) -> List[Video]:
        """查找高置信度分类的视频"""
        session = self.get_session()
        try:
            return session.query(Video).join(VideoClassification).filter(
                VideoClassification.confidence >= min_confidence
            ).all()
        finally:
            session.close()
    
    def search_by_filename(self, keyword: str) -> List[Video]:
        """根据文件名关键词搜索"""
        return self.find(filters={"file_name": {"like": keyword}})
    
    def get_statistics_by_format(self) -> Dict[str, Any]:
        """获取按格式分组的统计信息"""
        session = self.get_session()
        try:
            results = session.query(
                Video.format,
                func.count(Video.id).label('count'),
                func.sum(Video.file_size).label('total_size'),
                func.avg(Video.duration).label('avg_duration')
            ).group_by(Video.format).all()
            
            stats = {}
            for result in results:
                format_name = result.format or 'unknown'
                stats[format_name] = {
                    'count': result.count,
                    'total_size': result.total_size or 0,
                    'total_size_mb': (result.total_size or 0) / (1024 * 1024),
                    'avg_duration': float(result.avg_duration or 0)
                }
            
            return stats
        finally:
            session.close()
    
    def get_statistics_by_resolution(self) -> Dict[str, Any]:
        """获取按分辨率分组的统计信息"""
        session = self.get_session()
        try:
            results = session.query(
                Video.width,
                Video.height,
                func.count(Video.id).label('count')
            ).group_by(Video.width, Video.height).all()
            
            stats = {}
            for result in results:
                if result.width and result.height:
                    resolution = f"{result.width}x{result.height}"
                    stats[resolution] = result.count
            
            return stats
        finally:
            session.close()
    
    def get_statistics_by_category(self) -> Dict[str, Any]:
        """获取按分类分组的统计信息"""
        session = self.get_session()
        try:
            results = session.query(
                VideoClassification.category,
                func.count(VideoClassification.id).label('count'),
                func.avg(VideoClassification.confidence).label('avg_confidence')
            ).group_by(VideoClassification.category).all()
            
            stats = {}
            for result in results:
                stats[result.category] = {
                    'count': result.count,
                    'avg_confidence': float(result.avg_confidence or 0)
                }
            
            return stats
        finally:
            session.close()
    
    def get_processing_statistics(self) -> Dict[str, Any]:
        """获取处理统计信息"""
        total_count = self.count()
        processed_count = self.count(is_processed=True)
        unprocessed_count = self.count(is_processed=False)
        unavailable_count = self.count(is_available=False)
        
        return {
            'total_videos': total_count,
            'processed_videos': processed_count,
            'unprocessed_videos': unprocessed_count,
            'unavailable_videos': unavailable_count,
            'processing_rate': processed_count / total_count if total_count > 0 else 0,
            'availability_rate': (total_count - unavailable_count) / total_count if total_count > 0 else 0
        }
    
    def mark_as_processed(self, video_id: int) -> bool:
        """标记视频为已处理"""
        return self.update(video_id, is_processed=True) is not None
    
    def mark_as_unavailable(self, video_id: int, reason: str = None) -> bool:
        """标记视频为不可用"""
        update_data = {"is_available": False}
        if reason:
            update_data["notes"] = reason
        
        return self.update(video_id, **update_data) is not None
    
    def update_last_accessed(self, video_id: int) -> bool:
        """更新最后访问时间"""
        return self.update(video_id, last_accessed=datetime.utcnow()) is not None
    
    def cleanup_orphaned_metadata(self) -> int:
        """清理孤立的元数据记录"""
        session = self.get_session()
        try:
            # 删除没有对应视频的元数据
            orphaned_count = session.query(VideoMetadata).filter(
                ~VideoMetadata.video_id.in_(
                    session.query(Video.id)
                )
            ).count()
            
            session.query(VideoMetadata).filter(
                ~VideoMetadata.video_id.in_(
                    session.query(Video.id)
                )
            ).delete(synchronize_session=False)
            
            session.commit()
            return orphaned_count
        except Exception as e:
            session.rollback()
            raise e
        finally:
            session.close()
    
    def cleanup_orphaned_classifications(self) -> int:
        """清理孤立的分类记录"""
        session = self.get_session()
        try:
            # 删除没有对应视频的分类
            orphaned_count = session.query(VideoClassification).filter(
                ~VideoClassification.video_id.in_(
                    session.query(Video.id)
                )
            ).count()
            
            session.query(VideoClassification).filter(
                ~VideoClassification.video_id.in_(
                    session.query(Video.id)
                )
            ).delete(synchronize_session=False)
            
            session.commit()
            return orphaned_count
        except Exception as e:
            session.rollback()
            raise e
        finally:
            session.close()

    def create_metadata(self, **kwargs) -> VideoMetadata:
        """创建视频元数据"""
        session = self.get_session()
        try:
            metadata = VideoMetadata(**kwargs)
            session.add(metadata)
            session.commit()
            session.refresh(metadata)
            return metadata
        except Exception as e:
            session.rollback()
            raise e
        finally:
            session.close()

    def update_metadata(self, metadata: VideoMetadata) -> VideoMetadata:
        """更新视频元数据"""
        session = self.get_session()
        try:
            session.merge(metadata)
            session.commit()
            return metadata
        except Exception as e:
            session.rollback()
            raise e
        finally:
            session.close()

    def create_classification(self, **kwargs) -> VideoClassification:
        """创建视频分类"""
        session = self.get_session()
        try:
            classification = VideoClassification(**kwargs)
            session.add(classification)
            session.commit()
            session.refresh(classification)
            return classification
        except Exception as e:
            session.rollback()
            raise e
        finally:
            session.close()

    def update_classification(self, classification: VideoClassification) -> VideoClassification:
        """更新视频分类"""
        session = self.get_session()
        try:
            session.merge(classification)
            session.commit()
            return classification
        except Exception as e:
            session.rollback()
            raise e
        finally:
            session.close()

    def get_classification_statistics(self) -> Dict[str, int]:
        """获取分类统计信息"""
        session = self.get_session()
        try:
            results = session.query(
                VideoClassification.category,
                func.count(VideoClassification.id).label('count')
            ).group_by(VideoClassification.category).all()

            return {result.category: result.count for result in results}
        finally:
            session.close()

    def get_recent_videos(self, limit: int = 10) -> List[Video]:
        """获取最近的视频"""
        session = self.get_session()
        try:
            return session.query(Video).order_by(
                desc(Video.created_at)
            ).limit(limit).all()
        finally:
            session.close()

    def count_by_field(self, field_name: str, field_value: Any) -> int:
        """按字段值计数"""
        session = self.get_session()
        try:
            return session.query(Video).filter(
                getattr(Video, field_name) == field_value
            ).count()
        finally:
            session.close()

    def count_all(self) -> int:
        """计算总数"""
        session = self.get_session()
        try:
            return session.query(Video).count()
        finally:
            session.close()
